package com.itwzy.bookshop.DAO;

import com.itwzy.bookshop.Bean.BookTypeBean;
import com.itwzy.bookshop.Bean.PageBean;
import com.mysql.cj.util.DnsSrv;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class TypeDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入类型
     * @param type
     * @return
     */
    public int insertType(BookTypeBean type){
        String sql = "INSERT INTO book_type(type_name,type_depict) VALUES(?, ?)";
        return jdbcTemplate.update(sql,type.getTypeName(),type.getTypeDepict());
    }

    /**
     * 类型查询
     * @return
     */
    public List<BookTypeBean> selectTypes(){
        String sql = "SELECT type_id, type_name,type_depict FROM book_type";
        List<BookTypeBean> types = new ArrayList<>();
        types = jdbcTemplate.query(sql, new RowMapper<BookTypeBean>() {
            @Override
            public BookTypeBean mapRow(ResultSet rs, int i) throws SQLException {
                BookTypeBean type = new BookTypeBean();
                type.setTypeId(rs.getInt("type_id"));
                type.setTypeName(rs.getString("type_name"));
                type.setTypeDepict(rs.getString("type_depict"));
                return type;
            }
        });
        return types;
    }

    /**
     * 类型分页
     * @param page
     * @return
     */
    public List<BookTypeBean> selectTypesPages(PageBean page){
        String sql = "SELECT type_id, type_name,type_depict FROM book_type LIMIT ?,?";
        List<BookTypeBean> types = new ArrayList<>();
        types = jdbcTemplate.query(sql, new RowMapper<BookTypeBean>() {
            @Override
            public BookTypeBean mapRow(ResultSet rs, int i) throws SQLException {
                BookTypeBean type = new BookTypeBean();
                type.setTypeId(rs.getInt("type_id"));
                type.setTypeName(rs.getString("type_name"));
                type.setTypeDepict(rs.getString("type_depict"));
                return type;
            }
        },(page.getPageNow()-1)*page.getPageSize(),page.getPageSize());
        return types;
    }

    /**
     * 查询总行数
     */
    public int getRowCount(){
        String sql = "SELECT COUNT(type_id) FROM book_type";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    /**
     * 删除类型
     * @param id
     * @return
     */
    public int deleteType(Integer id){
        String sql = "DELETE FROM book_type WHERE type_id=?";
        return jdbcTemplate.update(sql,id);
    }


    /**
     * 修改类型
     * @param type
     * @return
     */
    public int updateType(BookTypeBean type){
        String sql = "UPDATE book_type SET type_name = ?,type_depict = ? WHERE type_id = ?";
        return jdbcTemplate.update(sql,type.getTypeName(),type.getTypeDepict(),type.getTypeId());
    }
}
